﻿using System;
using System.Collections.Generic;
using System.Text;
using LearnNet.DAL;
using LearnNet.Entity;
using System.Data;
using System.Data.Common;
namespace LearnNet.BLL
{
    public class BLLHelper
    {
        #region 当前数据库类型
        /// <summary>
        /// 返回数据库类型字符串形式
        /// </summary>
        /// <returns></returns>
        public static string GetDBType()
        {
            return DALHelper.LearnNetHelper.DatabaseType.ToString();
        }
        /// <summary>
        /// 备份数据库
        /// </summary>
        /// <param name="path">数据库备份地址</param>
        /// <returns></returns>
        public static Int32 BackUpData(string path)
        {
            return DALHelper.LearnNetHelper.ExecuteNonQuery(CommandType.Text, string.Concat("BACKUP DATABASE LearnNet TO DISK ='", path, "'"), null);
        }
        #endregion
        
        /// <summary>
        /// 返回查询的字段值
        /// </summary>
        /// <param name="sql">执行的语句</param>
        /// <returns>字符串类型</returns>
        public static string ExecuteScalar(string sql)
        {
            object obj = DALHelper.LearnNetHelper.ExecuteScalar(CommandType.Text, sql, null);
            if (obj == null)
            {
                return "";
            }
            else
            {
                return obj.ToString();
            }
        }
        #region 构造sql语句
        /// <summary>
        /// 获取分页SQL
        /// </summary>
        /// <param name="qp">查询条件类</param>
        /// <returns></returns>
        public static string GetPagerSQL(QueryParam qp)
        {
            return DALHelper.LearnNetHelper.GetPagerSQL(qp);
        }

        /// <summary>
        /// 时间转sql语句
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static string GetSqlDateTime(DateTime dt)
        {
            return DALHelper.LearnNetHelper.GetSqlDateTime(dt);
        }
        #endregion

        #region 更新字段方法
        /// <summary>
        /// 更新数据库字段
        /// </summary>
        /// <param name="table">表名</param>
        /// <param name="TableFiledsValue">更新字段和值</param>
        /// <param name="Where">更新条件</param>
        /// <returns></returns>
        public static int Update(string table, string TableFiledsValue, string Where)
        {
            return DALHelper.LearnNetHelper.ExecuteNonQuery(CommandType.Text, string.Concat("update ", table, " set ", TableFiledsValue, " where ", Where), null);

        }

        #endregion
       
        #region "sys_User(用户表) - Method"

        /// <summary>
        /// 删除指定ID的用户信息
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public static Int32 sys_UserDelete(int userId)
        {
            return DALHelper.LearnNetHelper.ExecuteNonQuery(CommandType.Text, string.Concat("delete from sys_user where UserID=", userId.ToString()), null);
        }

        /// <summary>
        /// 删除用户所有角色
        /// </summary>
        /// <param name="userId">用户id</param>
        /// <returns></returns>
        public static Int32 sys_UserRolesDelete(int userId)
        {
            return DALHelper.LearnNetHelper.ExecuteNonQuery(CommandType.Text, string.Concat("delete from sys_userRoles where r_UserId=", userId.ToString()), null);
        }

        /// <summary>
        /// 新增/删除/修改 sys_UserEntity (用户表)
        /// </summary>
        /// <param name="fam">sys_UserEntity实体类</param>
        /// <returns>-1:存储过程执行失败,-2:存在相同的主键,Insert:返回插入自动ID,Update:返回更新记录数,Delete:返回删除记录数</returns>
        public static Int32 sys_UserInsertUpdateDelete(sys_UserEntity fam)
        {
            return DALHelper.LearnNetHelper.sys_UserInsertUpdateDelete(fam);
        }
        /// <summary>
        /// 返回登录名
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <returns></returns>
        public static string sys_UserGetUserName(int userId)
        {
            object reObj = DALHelper.LearnNetHelper.ExecuteScalar(CommandType.Text, string.Concat("select top 1 U_loginname from sys_user where UserID=", userId.ToString()), null);
            if (reObj == null)
            {
                return "";
            }
            else
            {
                return reObj.ToString();
            }
        }
        /// <summary>
        /// 根据UserID返回 sys_UserEntity实体类 单笔资料 (用户表)
        /// </summary>
        /// <param name="UserID">UserID 用户ID号</param>
        /// <returns>返回 sys_UserEntity实体类 UserID为0则无记录</returns>
        public static sys_UserEntity sys_UserDisp(Int32 UserID)
        {
            sys_UserEntity fam = new sys_UserEntity();
            QueryParam qp = new QueryParam();
            qp.PageIndex = 1;
            qp.PageSize = 1;
            qp.Where = string.Format("{0}.{1} = {2}", "sys_User", "UserID", UserID);
            int RecordCount = 0;
            List<sys_UserEntity> lst = sys_UserList(qp, out RecordCount);
            if (RecordCount > 0)
            {
                fam = lst[0];
            }
            return fam;
        }

        /// <summary>
        /// 返回sys_UserEntity实体类的List对象 (用户表)
        /// </summary>
        /// <param name="qp">查询类(非安全函数,传入参数请进行Sql字符串过滤)</param>
        /// <param name="RecordCount">返回记录总数</param>
        /// <returns>sys_UserEntity实体类的List对象(用户表)</returns>
        public static List<sys_UserEntity> sys_UserList(QueryParam qp, out int RecordCount)
        {
            qp.TableName = "sys_User";
            if (qp.Orderfld == null)
            {
                qp.Orderfld = "UserID";
            }


            if (qp.ReturnFields == null)
            {
                qp.ReturnFields = "*";
            }
            return DALHelper.LearnNetHelper.sys_UserList(qp, true, out RecordCount);
        }

        /// <summary>
        /// 检测是否违反sys_User表的PK值
        /// </summary>
        /// <param name="fam">sys_UserTable类</param>
        /// <param name="pt">PopedomType类型，只对New,Edit有效</param>
        /// <returns></returns>
        public static bool sys_UserEntityCheckPK(sys_UserEntity fam, PopedomType pt)
        {
            QueryParam qp = new QueryParam();
            if (pt == PopedomType.Add)
                qp.Where = string.Format("  U_LoginName='{0}'", fam.U_LoginName);
            else if (pt == PopedomType.Edit)
                qp.Where = string.Format("  U_LoginName='{0}' and UserID<>{1} ", fam.U_LoginName, fam.UserID);

            int RecordCount = 0;
            sys_UserList(qp, out RecordCount);
            if (RecordCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        #endregion


    }
}
